﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Data.Sqlite;

namespace Launchkit_Backend.model
{
    class DBUtils
    {
        private static SqliteCommand sqlcmd;
        internal static void Init()
        {
            sqlcmd = conn.CreateCommand();
        }

        public static long allocateId() {
            SqliteDataReader dr = query("select id from Entries order by id desc limit 1 offset 0");
            dr.Read();
            long id = dr.GetInt64(0)+1;
            dr.Close();
            return id;
        }

        internal static void destory() {
            sqlcmd.Dispose();
        }
        internal static void InitDB()
        {
            Entry entry = new Entry(0, -1, Entry.TypeCollection, "主页", "house.png", "{}");

            addEntry(entry);
            Entry entryWelcome = new Entry(1, 0, Entry.TypeEntry, "欢迎使用", "auto_awesome.png", "{}");
            entryWelcome.EntryData.x = 0;
            entryWelcome.EntryData.y = 0;
            entryWelcome.EntryData.w = 3;
            entryWelcome.EntryData.h = 3;
            entryWelcome.EntryData.action = "null";
            entryWelcome.EntryData.param = "";
            addEntry(entryWelcome);

            entryWelcome = new Entry(2, 0, Entry.TypeEntry, "使用帮助", "auto_stories.png", "{}");
            entryWelcome.EntryData.x = 3;
            entryWelcome.EntryData.y = 0;
            entryWelcome.EntryData.w = 2;
            entryWelcome.EntryData.h = 2;
            entryWelcome.EntryData.action = EntryActions.ActionWebsite;
            entryWelcome.EntryData.param = "https://github.com/ZYFDroid";
            addEntry(entryWelcome);

            entryWelcome = new Entry(3, 0, Entry.TypeEntry, "作者主页", "house.png", "{}");
            entryWelcome.EntryData.x = 5;
            entryWelcome.EntryData.y = 0;
            entryWelcome.EntryData.w = 2;
            entryWelcome.EntryData.h = 2;
            entryWelcome.EntryData.action = EntryActions.ActionWebsite;
            entryWelcome.EntryData.param = "https://github.com/ZYFDroid";
            addEntry(entryWelcome);

            entryWelcome = new Entry(4, 0, Entry.TypeEntry, "开源地址", "desktop_mac.png", "{}");
            entryWelcome.EntryData.x = 7;
            entryWelcome.EntryData.y = 0;
            entryWelcome.EntryData.w = 2;
            entryWelcome.EntryData.h = 2;
            entryWelcome.EntryData.action = EntryActions.ActionWebsite;
            entryWelcome.EntryData.param = "https://github.com/ZYFDroid";
            addEntry(entryWelcome);
        }

        public static bool addEntry(Entry e) {
            bool isExists = false;
            using (SqliteDataReader dr = query("select id,parentid,type,displayname,displayicon,data from Entries where id = ?", e.ID))
            {
                if (dr.HasRows)
                {
                    isExists = true;
                }
            }
            if (isExists)
            {
                return exec("update Entries set parentid=?,type=?,displayname=?,displayicon=?,data=? where id=?",
                    e.ParentID,
                    e.Type,
                    e.DisplayName,
                    e.DisplayIcon,
                    e.Data,
                    e.ID
                    ) > 0;
            }
            else {
                return exec("insert into Entries(parentid,type,displayname,displayicon,data,id) values(?,?,?,?,?,?)",
                    e.ParentID,
                    e.Type,
                    e.DisplayName,
                    e.DisplayIcon,
                    e.Data,
                    e.ID
                    )>0;
            }
        }

        public static SqliteConnection conn;
        public static Entry getEntryById(long id)
        {
            using (SqliteDataReader dr = query("select id,parentid,type,displayname,displayicon,data from Entries where id = ?", id)){
                if (dr.HasRows) {
                    dr.Read();
                    return new Entry(dr.GetInt64(0), dr.GetInt64(1), dr.GetInt32(2), dr.GetString(3), dr.GetString(4), dr.GetString(5));
                }
                return null;
            }
        }
        public static List<Entry> listEntry(long parentid)
        {
            using (SqliteDataReader dr = query("select id,parentid,type,displayname,displayicon,data from Entries where parentid = ?", parentid))
            {
                List<Entry> entries = new List<Entry>();
                if (dr.HasRows)
                {
                    while (dr.Read()) {
                        entries.Add(new Entry(dr.GetInt64(0), dr.GetInt64(1), dr.GetInt32(2), dr.GetString(3), dr.GetString(4), dr.GetString(5)));
                    }
                }
                return entries;
            }
        }

        public static SqliteDataReader query(string sql, params object[] args) {
            sqlcmd.Parameters.Clear();
            string paramPrefix = "arg_";
            string[] sqlbreaks = sql.Split('?');
            StringBuilder sqlBuilder = new StringBuilder();
            for (int i = 0; i < sqlbreaks.Length; i++)
            {
                sqlBuilder.Append(sqlbreaks[i]);
                if (i != sqlbreaks.Length - 1)
                {
                    String paramname = "$" + paramPrefix + i;
                    sqlBuilder.Append(paramname);
                    sqlcmd.Parameters.AddWithValue(paramname, args[i]);
                }
            }
            sqlcmd.CommandText = sqlBuilder.ToString();
            return sqlcmd.ExecuteReader();
        }

        public static bool hasData(string sql, params object[] args) {
            using (SqliteDataReader dr = query(sql, args)) {
                return dr.HasRows;
            }
        }

        public static int exec(String sql, params object[] args)
        {
            sqlcmd.Parameters.Clear();
            string paramPrefix = "arg_";
            string[] sqlbreaks = sql.Split('?');
            StringBuilder sqlBuilder = new StringBuilder();
            for (int i = 0; i < sqlbreaks.Length; i++)
            {
                sqlBuilder.Append(sqlbreaks[i]);
                if (i != sqlbreaks.Length - 1)
                {
                    String paramname = "$" + paramPrefix + i;
                    sqlBuilder.Append(paramname);
                    sqlcmd.Parameters.AddWithValue(paramname, args[i]);
                }
            }
            sqlcmd.CommandText = sqlBuilder.ToString();
            return sqlcmd.ExecuteNonQuery();
        }

        private static readonly DateTime epoch = new DateTime(2021, 10, 30);
        public static long SysClock
        {
            get
            {
                return (long)(DateTime.Now - epoch).TotalMilliseconds;
            }
        }

        public static void recordHistory(Entry e) {
            if (hasData("select id from History where EntryId = ?", e.ID))
            {
                exec("update History set AccessTime=?,AccessCount = AccessCount+1 where EntryId = ?", SysClock,e.ID);
            }
            else {
                exec("insert into History(EntryID,AccessTime,AccessCount,DisplayName,DisplayIcon) values(?,?,?,?,?)",e.ID,SysClock,0,e.DisplayName,e.DisplayIcon);
            }
        }

        public static List<LeftListItem> getHistory() {
            List<LeftListItem> items = new List<LeftListItem>();
            long lastAccess = -1;
            using (SqliteDataReader dr = query("select EntryId,DisplayName,DisplayIcon,AccessTime from History order by AccessTime desc limit 8 offset 0"))
            {
                if (dr.HasRows)
                {
                    while (dr.Read())
                    {
                        LeftListItem item = new LeftListItem(dr.GetInt64(0), dr.GetString(1), dr.GetString(2));
                        if (lastAccess == -1)
                        {
                            lastAccess = dr.GetInt64(3);
                        }
                        items.Add(item);
                    }
                }
            }
            if (lastAccess>0) {
                long begin = lastAccess - 86400000L * 30L;
                using (SqliteDataReader dr = query("select EntryId,DisplayName,DisplayIcon,AccessTime from History where AccessTime > ? order by AccessCount limit 4 offset 0",begin))
                {
                    if (dr.HasRows)
                    {
                        while (dr.Read())
                        {
                            LeftListItem item = new LeftListItem(dr.GetInt64(0), dr.GetString(1), dr.GetString(2));
                            if (lastAccess == -1)
                            {
                                lastAccess = dr.GetInt64(3);
                            }
                            items.Insert(0,item);
                        }
                    }
                }
            }

            items = items.Distinct().ToList();
            return items;
        }

        public static bool isFav(Entry e) {
            return hasData("select id from Favourite where EntryId = ?", e.ID);
        }

        public static List<LeftListItem> getFav() {
            List<LeftListItem> items = new List<LeftListItem>();
            using (SqliteDataReader dr = query("select EntryId,DisplayName,DisplayIcon from Favourite")) {
                if (dr.HasRows)
                {
                    while (dr.Read()) {
                        LeftListItem item = new LeftListItem(dr.GetInt64(0), dr.GetString(1), dr.GetString(2));
                        items.Add(item);
                    }
                }
            }
            return items;
        }

        public static void addFav(Entry e) {
            if (!isFav(e))
            {
                exec("insert into Favourite(EntryId,DisplayName,DisplayIcon,Date) values(?,?,?,?)", e.ID, e.DisplayName, e.DisplayIcon, DateTime.Now);
            }
        }

        public static void removeFav(Entry e) {
            exec("delete from Favourite where EntryId = ?", e.ID);
        }

    }


    public class LeftListItem {
        public long EntryId;
        public String DisplayName, DisplayIcon;

        public LeftListItem(long entryId, string displayName, string displayIcon)
        {
            EntryId = entryId;
            DisplayName = displayName;
            DisplayIcon = displayIcon;
        }

        public override bool Equals(object obj)
        {
            return obj is LeftListItem item &&
                   EntryId == item.EntryId;
        }

        public override int GetHashCode()
        {
            return -1011078002 + EntryId.GetHashCode();
        }
    }
}
